/**
 * Created by Simon on 2017/4/14.
 */



let sqlite3 = require('sqlite3').verbose();
let Vegetable = require('../../model/vegetable-model');
let db = new sqlite3.Database('./database/king-land.db');
let _pageSize = 12;

let generateSearchSql = function (filter) {
  let pageNo = filter.pageNo;
  let generatedSql = `
    SELECT * FROM VEGETABLE WHERE
      1 = 1
      ${filter && filter.season ? 'AND SEASON = ?' : ''}
      ${filter && filter.type && (filter.type != 'all') ? 'AND TYPE = ?' : ''}
      LIMIT ${(pageNo - 1) * _pageSize}, ${_pageSize}
  `;
  return generatedSql;
};

let generatePageSql = function (filter) {
  let pageNo = filter.pageNo;
  let generatedSql = `
    SELECT count(*) as count FROM VEGETABLE WHERE
      1 = 1
      ${filter && filter.season ? 'AND SEASON = ?' : ''}
      ${filter && filter.type && (filter.type != 'all') ? 'AND TYPE = ?' : ''}
  `;
  return generatedSql;
};

/**
 *
 * @param filter
 *
 * 1|beans 豆类
 * 2|eggplant 茄果类
 * 3|fragrant 芳香植物
 * 4|leafy 叶菜类
 * 5|melon 瓜类
 * 6|root 根茎类
 * @param callback
 */

let getVegetablesByFilter = function (filter, callback) {
  let vegetables = [];
  let sql = generateSearchSql(filter);
  let pageSql = generatePageSql(filter);
  let sqlParam = [];
  filter.season && sqlParam.push(filter.season);
  filter.type && (filter.type != 'all') && sqlParam.push(filter.type);
  db.serialize(function () {
    let pagePromise = new Promise((resolve, reject) => {
      db.each(pageSql, sqlParam, function (err, v) {
        resolve(v);
      });
    });
    let dataPromise = new Promise((resolve, reject) => {
      db.each(sql, sqlParam, function (err, v) {
        vegetables.push(new Vegetable(v.id, v.cn_name, v.en_name, v.spa_name, v.cn_brief, v.en_brief, v.spa_brief, v.cn_method, v.en_method, v.spa_method, v.season, v.image_url, v.type));
      }, function () {
        resolve(vegetables);
      });
    });
    
    Promise.all([pagePromise, dataPromise]).then(values => {
      callback(values[1], values[0]['count']);
    });
    
  });
};

let getAllVegetables = function (callback) {
  let vegetables = [];
  db.serialize(function () {
    db.each(`SELECT * FROM VEGETABLE`, function (err, v) {
      vegetables.push(new Vegetable(v.id, v.cn_name, v.en_name, v.spa_name, v.cn_brief, v.en_brief, v.spa_brief, v.cn_method, v.en_method, v.spa_method, v.season, v.image_url, v.type, v.update_time));
    }, function () {
      callback(vegetables);
    });
  });
};

let getVegetableById = function (id, callback) {
  let vegetable = {};
  db.serialize(function () {
    db.each(`SELECT * FROM VEGETABLE where id = ?`, [id], function (err, v) {
      vegetable = new Vegetable(v.id, v.cn_name, v.en_name, v.spa_name, v.cn_brief, v.en_brief, v.spa_brief, v.cn_method, v.en_method, v.spa_method, v.season, v.image_url, v.type, v.update_time, v.status);
    }, function () {
      callback(vegetable);
    });
  });
};

let saveOrUpdate = function (vegetableModel, callback) {
  db.serialize(function () {
    var insertSql  = `
      INSERT INTO vegetable
        values
        (
          $id,
          $cnName,
          $enName,
          $spaName,
          $cnBrief,
          $enBrief,
          $spaBrief,
          $cnMethod,
          $enMethod,
          $spaMethod,
          $season,
          $status,
          datetime('now', '+8 hour'),
          $imageUrl,
          $type
         );
    `;
    var updateSql = `
      UPDATE vegetable SET
        cn_name = $cnName,
        en_name = $enName,
        spa_name = $spaName,
        update_time = datetime('now', '+8 hour'),
        status = $status,
        cn_brief = $cnBrief,
        en_brief = $enBrief,
        spa_brief = $spaBrief,
        cn_method = $cnMethod,
        en_method = $enMethod,
        spa_method = $spaMethod,
        season = $season,
        type = $type,
        image_url = $imageUrl
      where id = $id
    `;
    var prepareStmt = null;
    if(vegetableModel.id) {
      prepareStmt = db.prepare(updateSql);
    } else {
      prepareStmt = db.prepare(insertSql);
    }
    prepareStmt.run({
      $id: vegetableModel.id || null,
      $cnName: vegetableModel.cnName,
      $enName: vegetableModel.enName,
      $spaName: vegetableModel.spaName,
      $cnBrief: vegetableModel.cnBrief,
      $enBrief: vegetableModel.enBrief,
      $spaBrief: vegetableModel.spaBrief,
      $cnMethod: vegetableModel.cnMethod,
      $enMethod: vegetableModel.enMethod,
      $spaMethod: vegetableModel.spaMethod,
      $season: vegetableModel.season,
      $status: vegetableModel.status,
      $imageUrl: vegetableModel.imageUrl,
      $type: vegetableModel.type
    });
    prepareStmt.finalize();
  });

};

let deleteById = function (id, callback) {
  db.serialize(function () {
    db.run(`delete from vegetable where id = ?`, [id], callback);
  });
};

module.exports = {
  getVegetablesByFilter: getVegetablesByFilter,
  getAllVegetables: getAllVegetables,
  getVegetableById: getVegetableById,
  saveOrUpdate: saveOrUpdate,
  deleteById: deleteById
};